﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;

namespace IHISCETeim.DAL
{
    public class OUnsuitableProdDAL
    {
        /// <summary>
        /// 添加不适合品处理票
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="listImgs"></param>
        /// <param name="listDetail"></param>
        /// <returns></returns>
        public static int Add(OUnsuitableProdInfo obj,List<OUnsuitableProdImgInfo> listImgs,List<OUnsuitableProdDetailInfo> listDetail)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    //1.主表数据添加
                    DbCommand dbCommand = DB.GetStoredProcCommand("OUnsuitableProd_Add", obj.MacType, obj.SerialNumber, obj.IssueDate, obj.StampNo, obj.ItemCode, obj.Quantity, obj.ItemName, obj.SupplerName, obj.IsMultiplicity, obj.PartNum, obj.FindPrdWorkShop, obj.FindWarehouse, obj.ERPFinished, obj.ERPNotFinished, obj.Worker, obj.UnsuitableDescribe, obj.AnalysisCause, obj.Countermeasure, obj.Verification, obj.NotionReturn, obj.NotionReturnNum, obj.NotionOverhaul, obj.NotionOverhaulNum, obj.NotionSpecial, obj.NotionSpecialNum, obj.NotionScrap, obj.NotionScrapNum, obj.NotionEnter, obj.NotionBookYes, obj.NotionBookNo, obj.AnswerDate, obj.LoseSurveyUnit, obj.LoseSurveyPrice, obj.LoseSurveySubtotal, obj.LoseModifyUnit, obj.LoseModifyPrice, obj.LoseModifySubtotal, obj.LoseMaterialsTotal, obj.LoseDelayUnit, obj.LoseDelayPrice, obj.LoseDelaySubtotal, obj.LoseSaveUnit, obj.LoseSavePrice, obj.LoseSaveSubtotal, obj.LoseTotal, obj.LoseSuppler, obj.LoseCtrDpt,obj.NotionCanRepair,obj.AddType,obj.ExportFileName,obj.LoseModifyTitle,obj.LoseSurveyTitle,obj.LoseMaterialsRemark,obj.IsComplete,obj.IsSimple);
                    int UnsuitabledID = Convert.ToInt32(DB.ExecuteScalar(dbCommand, trans));//ID

                    //2.添加图像记录
                    foreach (OUnsuitableProdImgInfo itmimg in listImgs)
                    {
                        DbCommand dbCommandIMG = DB.GetStoredProcCommand("OUnsuitableProdImg_Add", UnsuitabledID, itmimg.Title, itmimg.IMGAbsolutePath, itmimg.IMGRelatively);
                        DB.ExecuteScalar(dbCommandIMG, trans);
                    }
                    //3.添加不良品明细
                    foreach (OUnsuitableProdDetailInfo itmdetail in listDetail)
                    {
                        DbCommand dbCommandDetail = DB.GetStoredProcCommand("OUnsuitableProdDetail_Add", UnsuitabledID, itmdetail.DetailType, itmdetail.DItemCode, itmdetail.DItemName, itmdetail.DQuantity, itmdetail.IsReturned, itmdetail.IsDefectivePrd, itmdetail.IsScrap);
                        DB.ExecuteScalar(dbCommandDetail, trans);
                    }
                    trans.Commit();
                    return UnsuitabledID;
                }
                catch (Exception exc)
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return 0;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
          
        }
        public static bool Update(OUnsuitableProdInfo obj, List<OUnsuitableProdImgInfo> listImgs, List<OUnsuitableProdDetailInfo> listDetail)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    //1.主表数据添加
                    DbCommand dbCommand = DB.GetStoredProcCommand("OUnsuitableProd_Update", obj.UnsuitabledID, obj.MacType, obj.SerialNumber, obj.IssueDate, obj.StampNo, obj.ItemCode, obj.Quantity, obj.ItemName, obj.SupplerName, obj.IsMultiplicity, obj.PartNum, obj.FindPrdWorkShop, obj.FindWarehouse, obj.ERPFinished, obj.ERPNotFinished, obj.Worker, obj.UnsuitableDescribe, obj.AnalysisCause, obj.Countermeasure, obj.Verification, obj.NotionReturn, obj.NotionReturnNum, obj.NotionOverhaul, obj.NotionOverhaulNum, obj.NotionSpecial, obj.NotionSpecialNum, obj.NotionScrap, obj.NotionScrapNum, obj.NotionEnter, obj.NotionBookYes, obj.NotionBookNo, obj.AnswerDate, obj.LoseSurveyUnit, obj.LoseSurveyPrice, obj.LoseSurveySubtotal, obj.LoseModifyUnit, obj.LoseModifyPrice, obj.LoseModifySubtotal, obj.LoseMaterialsTotal, obj.LoseDelayUnit, obj.LoseDelayPrice, obj.LoseDelaySubtotal, obj.LoseSaveUnit, obj.LoseSavePrice, obj.LoseSaveSubtotal, obj.LoseTotal, obj.LoseSuppler, obj.LoseCtrDpt, obj.NotionCanRepair, obj.ExportFileName, obj.LoseModifyTitle, obj.LoseSurveyTitle, obj.LoseMaterialsRemark, obj.IsComplete);
                    DB.ExecuteScalar(dbCommand, trans);                    
                    int UnsuitabledID = obj.UnsuitabledID;//ID
                    //2.删除明细及图像
                    DbCommand dbCommandDelIMG = DB.GetStoredProcCommand("OUnsuitableProdImg_DeleteByUnsuitabledID", UnsuitabledID);
                    DB.ExecuteScalar(dbCommandDelIMG, trans);
                    
                    DbCommand dbCommandDelDetail = DB.GetStoredProcCommand("OUnsuitableProdDetail_DeleteByUnsuitabledID", UnsuitabledID);
                    DB.ExecuteScalar(dbCommandDelDetail, trans);


                    //2.添加图像记录
                    foreach (OUnsuitableProdImgInfo itmimg in listImgs)
                    {
                        DbCommand dbCommandIMG = DB.GetStoredProcCommand("OUnsuitableProdImg_Add", UnsuitabledID, itmimg.Title, itmimg.IMGAbsolutePath, itmimg.IMGRelatively);
                        DB.ExecuteScalar(dbCommandIMG, trans);
                    }
                    //3.添加不良品明细
                    foreach (OUnsuitableProdDetailInfo itmdetail in listDetail)
                    {
                        DbCommand dbCommandDetail = DB.GetStoredProcCommand("OUnsuitableProdDetail_Add", UnsuitabledID, itmdetail.DetailType, itmdetail.DItemCode, itmdetail.DItemName, itmdetail.DQuantity, itmdetail.IsReturned, itmdetail.IsDefectivePrd, itmdetail.IsScrap);
                        DB.ExecuteScalar(dbCommandDetail, trans);
                    }
                    trans.Commit();
                    return true;
                }
                catch (Exception exc)
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }

        }
        /// <summary>
        /// 工作完成情况修改
        /// </summary>
        /// <param name="UnsuitabledID"></param>
        /// <param name="IsComplete"></param>
        /// <returns></returns>
        public static bool UpdateIsComplete(int UnsuitabledID , string IsComplete)
        {
            try
            {
                Database DB = DatabaseFactory.CreateDatabase();

                DbCommand dbCommand = DB.GetStoredProcCommand("OUnsuitableProd_UpdateIsComplete", UnsuitabledID, IsComplete);
                DB.ExecuteNonQuery(dbCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static bool Delete(int UnsuitabledID)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    DbCommand dbCommand = DB.GetStoredProcCommand("OUnsuitableProd_Delete", UnsuitabledID);
                    DB.ExecuteScalar(dbCommand, trans);
                    //2.删除明细及图像
                    DbCommand dbCommandDelIMG = DB.GetStoredProcCommand("OUnsuitableProdImg_DeleteByUnsuitabledID", UnsuitabledID);
                    DB.ExecuteScalar(dbCommandDelIMG, trans);

                    DbCommand dbCommandDelDetail = DB.GetStoredProcCommand("OUnsuitableProdDetail_DeleteByUnsuitabledID", UnsuitabledID);
                    DB.ExecuteScalar(dbCommandDelDetail, trans);


                    trans.Commit();
                    return true;
                }
                catch (Exception exc)
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }

        }

        public static IDataReader GetById(int UnsuitabledID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OUnsuitableProd_GetById", UnsuitabledID);
            return DB.ExecuteReader(dbCommand);
        }

        public static DataTable GetPagination(string whereSql, int PageIndex, int PageSize, string Sort, ref int TotalCount)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OUnsuitableProd_GetPagination");
            DB.AddInParameter(dbCommand, "whereSql", DbType.String, whereSql);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 8);
            DataTable dt = DB.ExecuteDataSet(dbCommand).Tables[0];

            TotalCount = Convert.ToInt32(DB.GetParameterValue(dbCommand, "TotalCount"));
            return dt;
        }

        /// <summary>
        /// 批量导出
        /// </summary>
        /// <param name="whereSql"></param>
        /// <returns></returns>
        public static DataTable GetBatchExportId(string whereSql)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OUnsuitableProd_GetBatchExportId");
            DB.AddInParameter(dbCommand, "whereSql", DbType.String, whereSql);
            DataTable dt = DB.ExecuteDataSet(dbCommand).Tables[0];
           
            return dt;
        }
        

      
        public static IDataReader OUnsuitableProdDetail_GetAllByUnsuitabledID(int UnsuitabledID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OUnsuitableProdDetail_GetAllByUnsuitabledID", UnsuitabledID);
            return DB.ExecuteReader(dbCommand);
        }
       
       
        public static IDataReader OUnsuitableProdImg_GetAllByUnsuitabledID(int UnsuitabledID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OUnsuitableProdImg_GetAllByUnsuitabledID", UnsuitabledID);
            return DB.ExecuteReader(dbCommand);
        }
        /// <summary>
        /// 添加供应商回复
        /// </summary>
        /// <param name="UnsuitabledID"></param>
        /// <param name="SupplerReplyFileName"></param>
        /// <param name="SupplerReplyFilePath"></param>
        /// <returns></returns>
        public static bool AddSupplerReply(int UnsuitabledID, string SupplerReplyFileName, string SupplerReplyFilePath, string ExcelJPFile, string ExcelJPFilePath)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            try
            {
                DbCommand dbCommand = DB.GetStoredProcCommand("OUnsuitableProd_AddSupplerReply", UnsuitabledID, SupplerReplyFileName, SupplerReplyFilePath, ExcelJPFile, ExcelJPFilePath);
                DB.ExecuteNonQuery(dbCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 财务部修改
	    ///索赔结算情况（C）			索赔收入差异(C-B)		不适合品物料处理方案
        /// </summary>
        /// <param name="UnsuitabledID"></param>
        /// <param name="ClaimRMB"></param>
        /// <param name="ClaimOpenDate"></param>
        /// <param name="ClaimPenZenHao"></param>
        /// <param name="ClaimAnalyze"></param>
        /// <param name="ProcessReason"></param>
        /// <param name="ProcessDocNum"></param>
        /// <param name="ProcessVoucherNum"></param>
        /// <returns></returns>
        public static bool UpdateClaim(OUnsuitableProdInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OUnsuitableProd_UpdateClaim", obj.UnsuitabledID, obj.ClaimRMB, obj.ClaimOpenDate, obj.ClaimPenZenHao, obj.ClaimAnalyze, obj.ProcessReason, obj.ProcessDocNum, obj.ProcessVoucherNum);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch (Exception exc)
            {
                return false;
            }
        }
        /// <summary>
        /// 质管部修改
	    /// 提交金额	提交时间
        /// </summary>
        /// <param name="UnsuitabledID"></param>
        /// <param name="SubmitJinE"></param>
        /// <param name="SubmitDate"></param>
        /// <returns></returns>
        public static bool UpdateSubmit(int UnsuitabledID, decimal SubmitJinE, string SubmitDate)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OUnsuitableProd_UpdateSubmit", UnsuitabledID, SubmitJinE, SubmitDate);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch (Exception exc)
            {
                return false;
            }
        }
        /// <summary>
        /// IK不适合品相关修改
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static bool UpdateIK(OUnsuitableProdInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OUnsuitableProd_UpdateIK", obj.UnsuitabledID, obj.IKQA, obj.IKCaseStatus, obj.IKReturnDate, obj.IKReturnTraCharges, obj.IKReturnGenerationDate, obj.IKReturnIHISCECharges, obj.IKRemark);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch (Exception exc)
            {
                return false;
            }
        }

    }
}
